import pandas as pd
from sqlalchemy import create_engine
#该脚本实现，已知idlist 将id对应的time和pa值都输出，没有过滤时间不同的情况

device_list = ['GW4684'
     #'GW161B',
# 'GW1626',
# 'GW1631',
# 'GW16D1',
# 'GW171C',
# 'GW1737',
# 'GW1923',
# 'GW1DB5',
# 'GW4020',
# 'GW40AE',
# 'GW419E',
# 'GW41C3',
# 'GW4225',
# 'GW428C',
# 'GW4459',
# 'GW447A',
# 'GW4597',
# 'GW4882',
# 'GW498F',
# 'GW50ED',
# 'GW5215',
# 'GW52B3',
# 'GW5375',
# 'GW53A5',
# 'GW53C9',
# 'GW5420',
# 'GW5524',
# 'GW561C',
# 'GW56A1',
# 'GW5814',
# 'GW5825',
# 'GWD14A',
# 'GWD1B2',
# 'GWD25F',
# 'GW5696',
]
placeholders = ','.join(f"'{i}'" for i in device_list)
# 创建数据库连接引擎
engine = create_engine("mysql+pymysql://tester:tester1234@localhost:3306/gwza_hardware_27?charset=utf8mb4")
#engine = create_engine("mysql+pymysql://gwza_hgc:test_hgc@192.168.3.12:3306/gwza_hardware?charset=utf8mb4")
#engine = create_engine("mysql+pymysql://tester:tester1234@192.168.3.28:3306/gwza231dev?charset=utf8mb4")
#engine = create_engine("mysql+pymysql://tester:tester1234@192.168.3.132:3306/174dev?charset=utf8mb4")
# 执行查询并读取数据
# query = f"""
# SELECT  device_id,
#         update_time AS time,
#         JSON_UNQUOTE(JSON_EXTRACT(memo, '$.pa')) AS pa
# FROM    map_device_his0924
# WHERE   device_id IN ({placeholders})
# AND     update_time BETWEEN '2025-09-24 15:58:48' AND '2025-09-24 16:04:17'
# ORDER BY device_id, update_time
# """

query = f"""
SELECT  device_id,
        update_time AS time,
        JSON_UNQUOTE(JSON_EXTRACT(memo, '$.pa')) AS pa
FROM    map_device_his
WHERE   device_id IN ({placeholders})
AND     update_time BETWEEN '2024-09-27 11:17:00' AND '2024-09-27 11:30:00'
ORDER BY device_id, update_time
"""
df = pd.read_sql(query, engine)
# 3. 给每个设备编号，方便横向摆放
df['dev_seq'] = df.groupby('device_id').cumcount()   # 0,1,2… 每个设备内部序号


# 4. 透视 → 行号是第 n 条记录，列是 device_id_time / device_id_pa
p_time = df.pivot(index='dev_seq', columns='device_id', values='time')
p_pa   = df.pivot(index='dev_seq', columns='device_id', values='pa')

# 5. 把 time、pa 交替合并
out = pd.DataFrame()
for col in p_time.columns:
    out[f'{col}_time'] = p_time[col]
    out[f'{col}_pa']   = p_pa[col]


# 6. 写 Excel
out.to_excel('device_time_pa.xlsx', index=False)